package com.iguo.news.util;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class NewsScoreGenerator {
    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://localhost:3306/tushu";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "20040302gyx";

    public static void main(String[] args) {
        NewsScoreGenerator generator = new NewsScoreGenerator();
        generator.generateScores();
    }

    public void generateScores() {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            // 1. 加载 JDBC 驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            // 3. 准备 SQL 语句（添加time字段）
            String sql = "INSERT INTO new_score (id, user_id, new_id, score, time) VALUES (?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);

            // 4. 初始化时间（第一条记录时间）
            LocalDateTime currentTime = LocalDateTime.of(2025, 5, 22, 0, 0, 0);
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

            // 5. 生成数据并执行 SQL
            Map<String, int[]> categories = new HashMap<>();
            categories.put("environment", new int[]{558, 757});
            categories.put("transport", new int[]{758, 971});
            categories.put("education", new int[]{972, 1191});
            categories.put("economy", new int[]{1192, 1516});
            categories.put("military", new int[]{1517, 1765});
            categories.put("sports", new int[]{1766, 2215});
            categories.put("medical", new int[]{2216, 2419});
            categories.put("art", new int[]{2420, 2667});
            categories.put("politics", new int[]{2668, 3172});

            Map<Integer, String> userPreferences = new HashMap<>();
            Map<String, List<Integer>> categoryUsers = new HashMap<>();
            for (String cat : categories.keySet()) {
                categoryUsers.put(cat, new ArrayList<>());
            }

            Random random = new Random();
            for (int i = 1; i <= 30; i++) {
                String cat = randomCategory(random, categories.keySet(), categoryUsers);
                if (cat == null) {
                    cat = new ArrayList<>(categories.keySet()).get(random.nextInt(categories.size()));
                }
                categoryUsers.get(cat).add(i);
                userPreferences.put(i, cat);
            }

            int recordId = 1;

            for (int userId = 1; userId <= 50; userId++) {
                String prefCat = userPreferences.get(userId);
                if (prefCat == null) {
                    prefCat = new ArrayList<>(categories.keySet()).get(random.nextInt(categories.size()));
                    userPreferences.put(userId, prefCat);
                }
                int[] prefRange = categories.get(prefCat);

                // 生成其他类别的评分（10%）
                int otherCount = 100 * 10 / 100;
                for (int j = 0; j < otherCount; j++) {
                    String otherCat;
                    do {
                        otherCat = new ArrayList<>(categories.keySet()).get(random.nextInt(categories.size()));
                    } while (otherCat.equals(prefCat));

                    int[] otherRange = categories.get(otherCat);
                    int newId = random.nextInt(otherRange[1] - otherRange[0] + 1) + otherRange[0];
                    int score = random.nextInt(3) + 1;

                    // 设置 SQL 参数（包括时间）
                    stmt.setInt(1, recordId++);
                    stmt.setInt(2, userId);
                    stmt.setInt(3, newId);
                    stmt.setInt(4, score);
                    stmt.setString(5, currentTime.format(formatter));

                    // 执行 SQL 并增加时间
                    stmt.executeUpdate();
                    currentTime = currentTime.plusSeconds(1); // 每条记录增加1秒
                }
                // 生成偏好类别的评分（90%）
                int prefCount = 100 * 90 / 100;
                for (int j = 0; j < prefCount; j++) {
                    int newId = random.nextInt(prefRange[1] - prefRange[0] + 1) + prefRange[0];
                    int score = random.nextInt(2) + 4;

                    // 设置 SQL 参数（包括时间）
                    stmt.setInt(1, recordId++);
                    stmt.setInt(2, userId);
                    stmt.setInt(3, newId);
                    stmt.setInt(4, score);
                    stmt.setString(5, currentTime.format(formatter));

                    // 执行 SQL 并增加时间
                    stmt.executeUpdate();
                    currentTime = currentTime.plusSeconds(1); // 每条记录增加1秒
                }


            }

            System.out.println("数据生成完成！共生成" + (recordId-1) + "条记录。");
            System.out.println("最后一条记录时间: " + currentTime.minusSeconds(1).format(formatter));

        } catch (ClassNotFoundException e) {
            System.err.println("MySQL JDBC 驱动未找到！");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("数据库操作失败！");
            e.printStackTrace();
        } finally {
            // 6. 关闭连接，释放资源
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static String randomCategory(Random random, Set<String> categories, Map<String, List<Integer>> categoryUsers) {
        List<String> availableCategories = new ArrayList<>();
        for (String cat : categories) {
            if (categoryUsers.get(cat).size() < 4) {
                availableCategories.add(cat);
            }
        }
        if (availableCategories.isEmpty()) {
            return null;
        }
        return availableCategories.get(random.nextInt(availableCategories.size()));
    }
}